/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseproject;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rohit Chand
 */
public class Interface {

    private static final String URL = "jdbc:derby://localhost:1527/ist411";
    private static final String USERNAME = "ist411";
    private static final String PASSWORD = "";
    private Connection con;
    public int addressCounter = 0;
    String addressName;
    public int orderCounter = 0;

    public Interface() throws SQLException {

        con = DriverManager.getConnection(URL);

        // Create tables if they do not already exist
        setupTables();

        // for initial testing start db off with consistent data
        initializeData();

    }//end Constructor Interface() 

// This method saves shipping data in the database 
    public ArrayList<Products> getAllSoftware(int catalog) throws SQLException {

        ArrayList< Products> results = null;
        ResultSet resultSet = null;

        try {
            Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
            resultSet = stmt.executeQuery("SELECT * FROM Products WHERE catalog_id =" + catalog);
            results = new ArrayList<Products>();

            while (resultSet.next()) {
                results.add(new Products(
                        resultSet.getString("Product_Name"),
                        resultSet.getString("Product_ImageLink"),
                        resultSet.getString("Product_Price"),
                        resultSet.getString("Product_description"),
                        resultSet.getInt("Catalog_id"),
                        resultSet.getInt("Product_id")));
            } // end whil

        } // end try
        catch (SQLException sqlException) {
            System.out.print("Error1");
            sqlException.printStackTrace();
        } // end catch
        finally {

            try {

                resultSet.close();
            } // end try
            catch (SQLException sqlException) {
                sqlException.printStackTrace();

            } // end catch
        } // end finally

        return results;
    } // end method getAllSoftware 

    public List<Products> getAllHardware() throws SQLException {

        List< Products> results = null;
        ResultSet resultSet = null;

        try {
            Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
            resultSet = stmt.executeQuery("SELECT * FROM Products WHERE catalog_id = 2");
            results = new ArrayList<Products>();

            while (resultSet.next()) {
                results.add(new Products(
                        resultSet.getString("name"),
                        resultSet.getString("Image_Link"),
                        resultSet.getString("Price"),
                        resultSet.getString("description"),
                        resultSet.getInt("catalog_id"),
                        resultSet.getInt("product_id")));
            } // end while
        } // end try
        catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } // end catch
        finally {
            try {
                resultSet.close();
            } // end try
            catch (SQLException sqlException) {
                sqlException.printStackTrace();
                con.close();
            } // end catch
        } // end finally

        return results;
    } // end method getAllSoftware 

    public List<Address> getAddress() throws SQLException {

        List< Address> results = null;
        ResultSet resultSet = null;

        try {
            Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
            resultSet = stmt.executeQuery("SELECT * FROM address");
            results = new ArrayList<Address>();

            while (resultSet.next()) {
                results.add(new Address(
                        resultSet.getInt("Address_Id"),
                        resultSet.getString("Address"),
                        resultSet.getString("City"),
                        resultSet.getString("State"),
                        resultSet.getString("Zip"),
                        resultSet.getString("Name"),
                        resultSet.getString("Email")));



            } // end while
        } // end try
        catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } // end catch
        finally {
            try {
                resultSet.close();
            } // end try
            catch (SQLException sqlException) {
                sqlException.printStackTrace();
                con.close();
            } // end catch
        } // end finally

        return results;
    } // end method getAllSoftware 

    public void insertAddress(String Address, String City, String State, String Zip, String Name, String Email) {
        addressCounter++;
        String address = Address;
        String city = City;
        String state = State;
        String zip = Zip;
        addressName = Name;
        String email = Email;
        try {
            Statement stmt = con.createStatement();

            stmt.executeUpdate("insert into app.Address values (" + addressCounter + ", '" + Email +"', '" + address + "', '" + city + "', '" + state + "', '" + zip + "', '" + addressName + "')");

        } catch (SQLException ex) {
            Logger.getLogger(Interface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//end insertAddress

    public void insertOrders(int TotalPrice) {
        orderCounter++;
        int totalPrice = TotalPrice;
        try {
            Statement stmt = con.createStatement();

            stmt.executeUpdate("insert into app.orders values (" + orderCounter + ", " + addressCounter + ", " + totalPrice + ")");


        } catch (SQLException ex) {
            Logger.getLogger(Interface.class.getName()).log(Level.SEVERE, null, ex);
        }

    }//end insertOrders()

    public void insertPurchasedItem(int Product_id, int Quantity) {

        int product_id = Product_id;
        int quantity = Quantity;
        try {
            Statement stmt = con.createStatement();

            stmt.executeQuery("insert into app.purchaseditem values (" + product_id + ", " + quantity + ", " + orderCounter + ")");


        } catch (SQLException ex) {
            Logger.getLogger(Interface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Products> getProduct(int productID) throws SQLException {

        ArrayList< Products> results = null;
        ResultSet resultSet = null;

        try {
            Statement stmt = con.createStatement(); // executeQuery returns ResultSet containing matching entries
            resultSet = stmt.executeQuery("SELECT * FROM Products WHERE product_id =" + productID);
            results = new ArrayList<Products>();

            while (resultSet.next()) {
                results.add(new Products(
                        resultSet.getString("Product_Name"),
                        resultSet.getString("Product_ImageLink"),
                        resultSet.getString("Product_Price"),
                        resultSet.getString("Product_Description"),
                        resultSet.getInt("Catalog_id"),
                        resultSet.getInt("Product_id")));
            } // end while

        } // end try
        catch (SQLException sqlException) {
            System.out.print("Error1");
            sqlException.printStackTrace();
        } // end catch
        finally {

            try {

                resultSet.close();
            } // end try
            catch (SQLException sqlException) {
                sqlException.printStackTrace();

            } // end catch
        } // end finally

        return results;
    } // end method getAllSoftware 

    private void setupTables() {

        try {
            ResultSet rs;

            DatabaseMetaData dbmd = con.getMetaData();
            Statement stmt = con.createStatement();

            rs = dbmd.getTables(null, null, "CATALOG", null);
            if (!rs.next()) {
                stmt.execute("CREATE TABLE CATALOG"
                        + "(Catalog_id integer,"
                        + "Catalog_Name varchar(50),"
                        + "Catalog_Description varchar(50),"
                        + " primary key (Catalog_id) )");

                System.out.println("Created CATALOG table");
            }
            rs = dbmd.getTables(null, null, "PRODUCTS", null);
            if (!rs.next()) {
                stmt.execute("CREATE TABLE PRODUCTS"
                        + "(Product_id integer,"
                        + "Catalog_id integer,"
                        + "Product_Name varchar(50),"
                        + "Product_ImageLink varchar(100),"
                        + "Product_Price numeric(10,2),"
                        + "Product_Description varchar(50),"
                        + "Primary key (Product_id) )");

                System.out.println("Created PRODUCTS table");
            }
            rs = dbmd.getTables(null, null, "PURCHASEDITEM", null);
            if (!rs.next()) {
                stmt.execute("CREATE TABLE PURCHASEDITEM"
                        + "(Product_id integer,"
                        + " Order_id integer,"
                        + " Quantity integer, "
                        + " primary key (Product_id, Order_id) )");

                System.out.println("Created PURCHASEDITEM table");
            }

            rs = dbmd.getTables(null, null, "ADDRESS", null);
            if (!rs.next()) {
                stmt.execute("CREATE TABLE ADDRESS "
                        + " (Address_id int,"
                        + " Email varchar(50),"
                        + " Street_Address varchar(50),"
                        + " City varchar(50),"
                        + " State char(2), "
                        + " Zip char(5),"
                        + " Name varchar(50),"
                        + " primary key (Address_id) )");

                System.out.println("Created ADDRESS table");
            }

            rs = dbmd.getTables(null, null, "ORDERS", null);
            if (!rs.next()) {
                stmt.execute("CREATE TABLE ORDERS "
                        + "(Order_id integer,"
                        + " Ship_Address_id integer, "
                        + " Total_Price numeric(10,2), "
                        + " primary key (Order_id) )");

                System.out.println("Created ORDERS table");
            }


            stmt.close();

            //   con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    private void initializeData() {
        try {
            ResultSet rs;

            Statement stmt = con.createStatement();
            
            stmt.execute("DELETE FROM CATALOG");
           
            stmt.execute("INSERT INTO CATALOG"
                    + " VALUES (1,'Hardware','Hardware Products')");
            stmt.execute("INSERT INTO CATALOG"
                    + " VALUES (2,'Software','Software Products')");
            
                   
            stmt.execute("DELETE FROM PRODUCTS");
            
            stmt.execute("INSERT INTO PRODUCTS VALUES (1,1,'Easy Fan','testing',32.95,'Computer Fan')");
            stmt.execute("INSERT INTO PRODUCTS VALUES (2,1,'acme keyboard','testing',9.95,'Keyboard')");

            stmt.execute("INSERT INTO PRODUCTS VALUES (3,2,'Adobe Photoshop CS6','testing',295.95,'Photo Editing Software')");
            stmt.execute("INSERT INTO PRODUCTS VALUES (4,2,'Microsoft SQL Server 2008','testing',99.95,'Database Management Software')");
            


            stmt.close();

            //   con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String args[]) {
        try {
            Interface test = new Interface();
            System.out.println(test.getProduct(2).get(0).getName());
        } catch (Exception x) {
        }
    }
}//end Class Interface 
